﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace DO.Common
{
	public class DataFunction : IDisposable 
	{

		private SqlConnection con;
		private string g_ConnectionString;

		/// <summary>
		/// 
		/// </summary>
		/// <param name="ConnectionString"></param>
		public DataFunction(string ConnectionString)
		{
			g_ConnectionString = ConnectionString;
		}


		#region RunSqlCommand ========================================================

		/// <summary>
		/// SQL Command
		/// </summary>
		/// <param name="strSQL"></param>
		/// <returns></returns>
		public bool RunSqlCommand(string strSQL)
		{
			SqlTransaction myTrans = null;
			SqlCommand cmd;

			ReOpen();
			cmd = new SqlCommand(strSQL, con);
			cmd.CommandTimeout = 0;
			myTrans = con.BeginTransaction();

			cmd.Transaction = myTrans;
			cmd.ExecuteNonQuery();

			myTrans.Commit();
			return true;
		}


		/// <summary>
		/// Run SQL Command, Get DataSet
		/// </summary>
		/// <param name="strSQL"></param>
		/// <param name="DataSet"></param>
		/// <returns></returns>
		public bool RunSqlCommand(string strSQL, out DataSet dataset)
		{
			bool status = false;
			DataSet myDataSet = new DataSet();
			try
			{
				ReOpen();
				try
				{
					SqlCommand cmd = new SqlCommand(strSQL, con);
					cmd.CommandTimeout = 0;
					SqlDataAdapter adapter = new SqlDataAdapter(cmd);
					adapter.Fill(myDataSet);
					status = true;
				}
				catch (Exception e)
				{
					myDataSet.Clear();
					status = false;
					Common.Trace.ErrorWrite("Datafunction", "Description:" + e.ToString() + "\r\n SQL = " + strSQL);
					throw e;
				}
			}
			catch (Exception e)
			{
				myDataSet.Clear();
				status = false;
				throw e;
			}

			dataset = myDataSet;
			return status;
		}
		
		#endregion

		private void ReOpen()
		{
			for (int i = 0; i < 4; i++)
			{
				try
				{
					// open connection
					if (con == null)
					{
						con = new SqlConnection(g_ConnectionString);
						con.Open();
					}
					else if (con.State.ToString().ToLower() == "closed")
					{
						con.Open();
					}
					break;
				}
				catch (SqlException e)
				{
					int TimeWait = 0; // 秒
					if (i == 0)
						TimeWait = 10;
					else if (i == 1)
						TimeWait = 30;
					else if (i == 2)
						TimeWait = 60;
					else // 若 超過 3 次皆失敗 則 直接送出錯誤訊息 結束程式
					{
						Common.Trace.ErrorWrite("Datafunction", "Connection Open Error(網路連線問題)Retry(" + i + "):" + " ErrorNumber=" + e.Number + "  " + e.ToString());
						throw e;
					}

					System.Threading.Thread.Sleep(1000 * TimeWait);
				}

			}// end for
		}

		public void Dispose()
		{
			// make sure connection is closed
			if (con != null)
			{
				con.Close();
				con.Dispose();
				con = null;
			}
		}
	}
}
